﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace studentmanagementdemo
{
    /// <summary>
    /// 
    /// </summary>
    public partial class frmchondetai : Form
    {

        private SqlConnection con;
        private DataTable dtsinhvien = new DataTable("tblStudent");
        private DataTable dtproject = new DataTable("tblProject");
        private DataTable dtstudentproject = new DataTable("tblStudentProject");
        private DataTable dtkt = new DataTable("tblStudentProject");
        private SqlDataAdapter da = new SqlDataAdapter();
        private Boolean kt;

        private void connect()
        {
            //String cn = "Data Source=127.0.0.1;Initial Catalog=iPMAC;Persist Security Info=True;User ID=sa;Password=sa";
            String cn = @"Data Source=(localdb)\linh123;Initial Catalog=iPMAC;Integrated Security=True";
            try
            {
                con = new SqlConnection(cn);
                con.Open(); //Moi ket noi                
            }
            catch (Exception ex)
            {
                MessageBox.Show("Không thể kết nối tới cơ sở dữ liệu !", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void disconnect()
        {
            con.Close(); //Dong ket noi
            con.Dispose(); //Giai phong tai nguyen
            con = null; //Huy doi tuong
        }

        private void getdata()
        {
            SqlCommand command = new SqlCommand(); //Khai bao 1 command
            command.Connection = con; //Ket noi
            command.CommandType = CommandType.Text; //Khai bao kieu command
            command.CommandText = @"SELECT (tblStudent.fldFirstName + ' ' 
                                        + tblStudent.fldLastName) AS HoTenSV,
                                        tblProject.fldName AS DeTai,
                                        tblProject.fldDescription AS DienGiai,
                                        tblStudentProject.fldStudentID AS MaSV,
                                        tblStudentProject.fldProjectID AS MaDeTai,
                                        tblStudentProject.fldID AS MaSVDT
                                    FROM tblStudentProject INNER JOIN dbo.tblStudent 
                                        ON tblStudentProject.fldStudentID = tblStudent.fldID 
                                        INNER JOIN dbo.tblProject 
                                        ON tblStudentProject.fldProjectID = tblProject.fldID"; //Cau truy van SQL
            da.SelectCommand = command; //Gan command cho da            
            da.Fill(dtstudentproject); //Nap du lieu vao table
            grvchondetai.DataSource = dtstudentproject; //Load du lieu len DataGridView             

            command.CommandText = @"Select (fldFirstName + ' ' + fldLastName) as  HoTenSV,
                                        fldID as MaSV
                                    From tblStudent";
            da.SelectCommand = command;
            da.Fill(dtsinhvien);
            cbohotensv.DataSource = dtsinhvien;
            cbohotensv.DisplayMember = "HoTenSV";
            cbohotensv.ValueMember = "MaSV";

            command.CommandText = "Select * From tblProject";
            da.SelectCommand = command;
            da.Fill(dtproject);
            cbodetai.DataSource = dtproject;
            cbodetai.DisplayMember = "fldName";
            cbodetai.ValueMember = "fldID";
        }

        public frmchondetai()
        {
            InitializeComponent();
        }

        private void binding()
        {
            cbohotensv.DataBindings.Clear();
            cbohotensv.DataBindings.Add("Text", grvchondetai.DataSource, "HoTenSV");
            cbodetai.DataBindings.Clear();
            cbodetai.DataBindings.Add("Text", grvchondetai.DataSource, "DeTai");
            txtdiengiai.DataBindings.Clear();
            txtdiengiai.DataBindings.Add("Text", cbodetai.DataSource, "fldDescription");
            txtmasvdt.DataBindings.Clear();
            txtmasvdt.DataBindings.Add("Text", grvchondetai.DataSource, "MaSVDT");
        }

        private void frmchondetai_Load(object sender, EventArgs e)
        {
            connect();
            getdata();
            binding();
        }

        private void btnthoat_Click(object sender, EventArgs e)
        {
            Close();
            Dispose();
            disconnect();
        }

        private bool kiemtra()
        {
            kt = false;
            SqlCommand command = new SqlCommand(); //Khai bao 1 command
            command.Connection = con; //Ket noi
            command.CommandType = CommandType.Text; //Khai bao kieu command
            command.CommandText = @"Select * From tblStudentProject 
                                             Where (fldStudentID = @fldStudentID)"; //Cau truy van SQL
            command.Parameters.Add("@fldStudentID", SqlDbType.Int, 50).Value = cbohotensv.SelectedValue;
            da.SelectCommand = command; //Gan command cho da            
            da.Fill(dtkt); //Nap du lieu vao table

            if (dtkt.Rows.Count > 0)
            {
                kt = true;
                txtmasvdt.DataBindings.Clear();
                txtmasvdt.DataBindings.Add("Text", dtkt, "fldID");
            }
            return kt;
        }

        private void btnchon_Click(object sender, EventArgs e)
        {
            kiemtra();
            if (kt == false)
            {
                DataRow row = dtstudentproject.NewRow();
                row["MaSV"] = cbohotensv.SelectedValue;
                row["MaDeTai"] = cbodetai.SelectedValue;
                dtstudentproject.Rows.Add(row);
                SqlCommand commandInsert = new SqlCommand();
                commandInsert.Connection = con;
                commandInsert.CommandType = CommandType.Text;
                commandInsert.CommandText = @"Insert tblStudentProject (fldStudentID, fldProjectID) 
                                                       Values(@MaSV, @MaDeTai)";
                commandInsert.Parameters.Add("@MaSV", SqlDbType.Int, 50, "MaSV");
                commandInsert.Parameters.Add("@MaDeTai", SqlDbType.Int, 50, "MaDeTai");
                da.InsertCommand = commandInsert;
                da.Update(dtstudentproject);
                MessageBox.Show("Bạn đã chọn đề tài thành công !", "THÔNG BÁO", MessageBoxButtons.OK);
            }
            else
            {
                if (MessageBox.Show("SV này đã chọn đề tài, bạn có muốn cập nhật lại đề tài cho SV này ko ?", "THOÁT", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                {
                    DataRow row = dtstudentproject.Select("MaSVDT = " + Convert.ToInt32(txtmasvdt.Text))[0];
                    row.BeginEdit();
                    row["MaSV"] = cbohotensv.SelectedValue;
                    row["MaDeTai"] = cbodetai.SelectedValue;
                    row.EndEdit();
                    SqlCommand commandUpdate = new SqlCommand();
                    commandUpdate.Connection = con;
                    commandUpdate.CommandType = CommandType.Text;
                    commandUpdate.CommandText = @"Update tblStudentProject Set fldStudentID = @MaSV, 
                                                                        fldProjectID=@MaDeTai
                                                                        Where fldID = @MaSVDT";
                    commandUpdate.Parameters.Add("@MaSVDT", SqlDbType.Int, 50, "MaSVDT");
                    commandUpdate.Parameters.Add("@MaSV", SqlDbType.Int, 50, "MaSV");
                    commandUpdate.Parameters.Add("@MaDeTai", SqlDbType.Int, 50, "MaDeTai");
                    da.UpdateCommand = commandUpdate;
                    da.Update(dtstudentproject);
                    MessageBox.Show("Bạn đã cập nhật đề tài thành công !", "THÔNG BÁO", MessageBoxButtons.OK);
                }
            }
            dtstudentproject.Clear();
            dtproject.Clear();
            dtsinhvien.Clear();
            dtkt.Clear();
            getdata();
            binding();
        }
    }
}
